package web.java.c_PreparedStatement;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.Test;

import web.java.z_JDBCUtil.JDBCUtil;

public class Demo01_PreparedStatement {
	
	/**
	 * 使用PreparedStatement进行数据操作
	 * */
	@Test
	public void add(){
		Connection conn=JDBCUtil.connection();
		PreparedStatement stmt=null;
		try{
			String sql="insert into test1(name, age) values(?,?)";
			stmt=conn.prepareStatement(sql);
			stmt.setString(1,"高耀");
			stmt.setInt(2, 15);
			int result=stmt.executeUpdate();
			System.out.println("影响了"+result+"行。");
			JDBCUtil.closeConn(conn, stmt);
		}catch(SQLException e){
			System.out.println("添加数据失败！"+e.getMessage());
			throw new RuntimeException(e);
		}
	}
	/**
	 * 修改
	 * */
	@Test
	public void modify(){
		Connection conn=JDBCUtil.connection();
		PreparedStatement stmt=null;
		try{
			String sql="update test1 set name=? where id=?";
			stmt=conn.prepareStatement(sql);
			stmt.setString(1,"刘天成");
			stmt.setInt(2, 5);
			int result=stmt.executeUpdate();
			System.out.println("影响了"+result+"行。");
			this.select();
			JDBCUtil.closeConn(conn, stmt);
		}catch(SQLException e){
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}
	/**
	 * 删除
	 * */
	@Test
	public void delete(){
		Connection conn=JDBCUtil.connection();
		PreparedStatement stmt=null;
		try{
			String sql="delete from test1 where id=?";
			stmt=conn.prepareStatement(sql);
			stmt.setInt(1,5);
			int result=stmt.executeUpdate();
			System.out.println("影响了"+result+"行");
			this.select();
			JDBCUtil.closeConn(conn, stmt);
		}catch(SQLException e){
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}
	/**
	 * 查看数据表
	 * */
	@Test
	public void select(){
		Connection conn=JDBCUtil.connection();
		PreparedStatement stmt=null;
		ResultSet result=null;
		try{
			String sql="select * from test1";
			stmt=conn.prepareStatement(sql);
			result=stmt.executeQuery();
			while(result.next()){
				System.out.print(result.getInt(1)+"\t");
				System.out.print(result.getString(2)+"\t");
				System.out.print(result.getInt(3)+"\t");
				System.out.println();
			}
			JDBCUtil.closeConn(conn, stmt, result);
		}catch(SQLException e){
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}
}
